package com.version1_4.dao.implement;

import com.version1_4.dao.TopicDao;
import org.apache.commons.collections.map.HashedMap;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by reeveslee on 16/4/27.
 */
@Repository
public class TopicDaoImpl implements TopicDao {
    private static final Logger LOGGER = Logger.getLogger(TopicDaoImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> getBanner() {
        String sql = "SELECT image AS img,type,link AS href,topicId FROM topic_banner";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> topicPage(int lastId) {

        if (lastId != 0) {
            String sql = "SELECT id,tag,image,title,secondary FROM topic WHERE orders > (SELECT orders FROM topic WHERE id=?) ORDER BY orders ASC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{lastId, 5});
        } else {
            String sql = "SELECT id,tag,image,title,secondary FROM topic ORDER BY orders ASC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{5});
        }
    }

    @Override
    public Map<String, Object> topicInfo(int topicId) {
        try {
            String sql = "SELECT id,tag,image AS img,title,secondary,sponsorId,type FROM topic WHERE id=?";
            return jdbcTemplate.queryForMap(sql, new Object[]{topicId});
        } catch (EmptyResultDataAccessException exception) {
            LOGGER.info("根据topicId无法找到对应话题信息", exception);
            return null;
        }
    }

    @Override
    public boolean follow(int topicId, int userId) {
        String sql = "SELECT id FROM topic_follow WHERE userId=? AND topicId=?";
        try {
            int id = jdbcTemplate.queryForObject(sql, new Object[]{userId, topicId}, java.lang.Integer.class);
            return true;
        } catch (EmptyResultDataAccessException exception) {
            return false;
        }
    }

    @Override
    public Map<String, Object> sponsor(int userId) {
        try {
            String sql = "SELECT userId,userPhoto AS photo,userName AS name,description AS descri FROM user_info WHERE userId=?";
            return jdbcTemplate.queryForMap(sql, new Object[]{userId});
        } catch (Exception exception) {
            return null;
        }
    }

    @Override
    public Map<String, Object> invite(int topicId) {
        try {
            String sql = "SELECT id,userId,userPhoto AS photo,userName AS name,text,image,voice,praise,time FROM feed,user_info WHERE feed.user=user_info.userId AND topicId=? AND invite=1";
            return jdbcTemplate.queryForMap(sql, new Object[]{topicId});
        } catch (EmptyResultDataAccessException exception) {
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> feeds(int topicId, int lastId) {
        try {

            if (lastId != 0) {
                String sql = "SELECT id,topicId,userId,name,photo,type,text,image,voice,time,reply,praise FROM (SELECT @rownum:=@rownum+1 AS rownum,id,topicId,userId,userName AS name,userPhoto AS photo,type,text,image,voice,time,reply,praise,invite FROM feed,user_info,(SELECT @rownum:=0) row WHERE feed.user=user_info.userId AND topicId=? ORDER BY praise DESC,id ASC) temp WHERE temp.invite=0 AND temp.rownum>(SELECT rownum FROM (SELECT @rownum:=@rownum+1 AS rownum,id,topicId FROM feed,user_info,(SELECT @rownum:=0) row WHERE feed.user=user_info.userId AND topicId=? ORDER BY praise DESC,id ASC) temp1 WHERE temp1.id=?) LIMIT ?";
                return jdbcTemplate.queryForList(sql, new Object[]{topicId, topicId, lastId, 20});
            } else {
                String sql = "SELECT id,topicId,userId,userName AS name,userPhoto AS photo,type,text,image,voice,time,reply,praise FROM feed,user_info WHERE feed.user=user_info.userId AND invite=0 AND topicId=? ORDER BY praise DESC,id ASC LIMIT ?";
                return jdbcTemplate.queryForList(sql, new Object[]{topicId, 20});
            }
        } catch (Exception exception) {
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> newFeeds(int topicId, int lastId) {
        String sql = "";
        if (lastId == 0) {
            sql = "SELECT id,topicId,userId,userName AS name,userPhoto AS photo,type,text,image,voice,time,reply,praise FROM feed,user_info WHERE topicId=? AND feed.user=user_info.userId ORDER BY id DESC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{topicId, 20});
        } else {
            sql = "SELECT id,topicId,userId,userName AS name,userPhoto AS photo,type,text,image,voice,time,reply,praise FROM feed,user_info WHERE topicId=? AND feed.user=user_info.userId AND id < ? ORDER BY id DESC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{topicId, lastId, 20});
        }
    }

    @Override
    public boolean hasPraised(int userId, int feedId) {
        String sql = "SELECT id FROM feed_praise WHERE userId=? AND feedId=?";
        try {
            jdbcTemplate.queryForObject(sql, new Object[]{userId, feedId}, java.lang.Integer.class);
            return true;
        } catch (EmptyResultDataAccessException exception) {
            return false;
        }
    }

    @Override
    public boolean addFollow(int userId, int topicId) {

        try {
            String sql = "SELECT id FROM topic_follow WHERE userId=? AND topicId=?";
            jdbcTemplate.queryForObject(sql, new Object[]{userId, topicId}, java.lang.Integer.class);
            return false;
        } catch (EmptyResultDataAccessException exception) {
            String sql = "INSERT INTO topic_follow(userId,topicId,time) VALUES (?,?,?)";
            int row = jdbcTemplate.update(sql, new Object[]{userId, topicId, new Date()});
            return (row == 1) ? true : false;
        }
    }

    @Override
    public boolean removeFollow(int userId, int topicId) {
        String sql = "DELETE FROM topic_follow WHERE userId=? AND topicId=?";
        try {
            jdbcTemplate.update(sql, new Object[]{userId, topicId});
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public List<Map<String, Object>> followTopicId(int userId) {
        String sql = "SELECT topicId,time FROM topic_follow WHERE userId=? ORDER BY time DESC";
        return jdbcTemplate.queryForList(sql, new Object[]{userId});
    }

    @Override
    public int feedCount(int topicId) {
        String sql = "SELECT COUNT(id) FROM feed WHERE topicId=?";
        return jdbcTemplate.queryForObject(sql, new Object[]{topicId}, java.lang.Integer.class);
    }

    @Override
    public int userCount(int topicId) {
        String sql = "SELECT count(temp.id) FROM (SELECT id FROM feed GROUP BY user,topicId HAVING topicId=?) temp";
        return jdbcTemplate.queryForObject(sql, new Object[]{topicId}, java.lang.Integer.class);
    }

    @Override
    public List<Map<String, Object>> search(String key) {
        try {
            String sql = "SELECT id,tag,title,secondary,image,time FROM topic WHERE title LIKE '%" + key + "%' ORDER BY time DESC";
            return jdbcTemplate.queryForList(sql);
        } catch (DataAccessException exception) {
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> tag(String name, int lastId) {
        if (lastId == 0) {
            String sql = "SELECT topic.id,title,secondary,image,time FROM topic WHERE topic.tag=? ORDER BY time DESC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{name, 5});
        } else {
            String sql = "SELECT id,title,secondary,image,time FROM topic WHERE topic.id<? AND topic.tag=? ORDER BY time DESC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{lastId, name, 5});

        }
    }

    @Override
    public Map<String, Object> tagInfo(String name) {
        try {
            String sql = "SELECT icon,banner FROM topic_tag WHERE name=?";
            return jdbcTemplate.queryForMap(sql, new Object[]{name});
        } catch (DataAccessException exception) {
            Map<String, Object> tagInfo = new HashedMap();
            tagInfo.put("icon", "");
            tagInfo.put("banner", "");
            return tagInfo;
        }
    }

    @Override
    public List<Map<String, Object>> recomTag() {
        try {
            String sql = "SELECT name,icon FROM topic_tag ORDER BY orders ASC";
            return jdbcTemplate.queryForList(sql);
        } catch (DataAccessException exception) {
            return null;
        }
    }

}
